import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import pandas as pd
import os
import json
import re

class CheckTab(ttk.Frame):
    def __init__(self, master):
        super().__init__(master)
        self.file_path = None
        self.df = None
        self.selected_column = tk.StringVar()
        self.create_widgets()

    def create_widgets(self):
        top_frame = ttk.Frame(self)
        top_frame.pack(fill=tk.X, padx=10, pady=10)
        self.upload_btn = ttk.Button(top_frame, text="上传Excel", command=self.upload_excel)
        self.upload_btn.pack(side=tk.LEFT, padx=(0, 10))
        self.header_label = ttk.Label(top_frame, text="选择比对字段：")
        self.header_label.pack(side=tk.LEFT)
        self.header_combo = ttk.Combobox(top_frame, textvariable=self.selected_column, state='readonly')
        self.header_combo.pack(side=tk.LEFT, padx=(5, 0))
        self.header_combo.bind('<<ComboboxSelected>>', self.on_column_selected)
        self.check_btn = ttk.Button(top_frame, text="开始核对", command=self.start_check, state='disabled')
        self.check_btn.pack(side=tk.LEFT, padx=(10,0))
        self.export_btn = ttk.Button(top_frame, text="导出结果", command=self.export_result, state='disabled')
        self.export_btn.pack(side=tk.LEFT, padx=(10,0))
        self.status_label = ttk.Label(self, text="等待操作", anchor='w')
        self.status_label.pack(fill=tk.X, padx=10, pady=(0,5))

    def upload_excel(self):
        file_path = filedialog.askopenfilename(filetypes=[('Excel Files', '*.xlsx'), ('Excel Files', '*.xls')])
        if not file_path:
            return
        try:
            df = pd.read_excel(file_path)
            self.df = df
            self.file_path = file_path
            self.header_combo['values'] = list(df.columns)
            if len(df.columns) >= 2:
                self.selected_column.set(df.columns[1])
            elif len(df.columns) == 1:
                self.selected_column.set(df.columns[0])
            else:
                self.selected_column.set('')
            self.check_btn['state'] = 'normal' if len(df.columns) > 0 else 'disabled'
            self.status_label['text'] = f"已加载文件：{os.path.basename(file_path)}"
        except Exception as e:
            messagebox.showerror('错误', f'读取Excel失败：{e}')

    def on_column_selected(self, event=None):
        pass  # 预留

    def start_check(self):
        self.status_label['text'] = "正在核对..."
        self.check_btn['state'] = 'disabled'
        self.export_btn['state'] = 'disabled'
        self.after(100, self.do_check)

    def do_check(self):
        # 读取jsons目录下所有json
        json_dir = os.path.abspath('jsons')
        json_files = [f for f in os.listdir(json_dir) if f.endswith('.json')]
        json_data = {}
        for f in json_files:
            try:
                with open(os.path.join(json_dir, f), 'r', encoding='utf-8') as jf:
                    data = json.load(jf)
                    if data.get('状态', '').startswith('成功签收'):
                        # 只要 轨迹 第一个元素的 内容
                        json_data[data.get('单号')] ={
                            '单号': data.get('单号'),
                            '轨迹': data.get('轨迹', [])[0].get('内容', '')
                        }
            except Exception:
                continue
        # 只保留状态为成功签收的
        self.match_result = self.df.copy()
        match_col = self.selected_column.get()
        # 标记核对结果
        self.match_result['__核对结果__'] = ''
        self.match_result['__轨迹内容__'] = ''
        # 记录需高亮的单元格 (row_idx, col_name)
        self.highlight_cells = set()
        print('表头:', self.match_result.columns.tolist())
        for idx, row in self.match_result.iterrows():
            danhao = str(row[match_col]) if match_col in row else None
            if danhao and danhao in json_data:
                self.match_result.at[idx, '__核对结果__'] = '已核对'
                # 轨迹第一个内容
                content = json_data[danhao].get('轨迹', '')
                if content:
                    self.match_result.at[idx, '__轨迹内容__'] = content
                    # 按第一个逗号分隔提取城市、州、邮编
                    city, state, zipcode = '', '', ''
                    if ',' in content:
                        left, right = content.split(',', 1)
                        city = left.strip()
                        right = right.strip()
                        # right 可能是 "sNC 2272826, Delivered..."
                        parts = right.split()
                        if len(parts) >= 2:
                            state = parts[0].strip()
                            zipcode = parts[1].strip(',')
                    print(f'单号:{danhao} 轨迹提取:({city},{state},{zipcode})')
                    # 比对Excel字段，忽略大小写和前后空格
                    for col, val in [('收件人城市', city), ('收件人州', state), ('收件人邮编', zipcode)]:
                        if col in self.match_result.columns:
                            excel_val = str(row[col]).strip() if pd.notna(row[col]) else ''
                            print(f'  比对字段:{col} excel值:[{excel_val}] 轨迹值:[{val}]')
                            # 只要轨迹值为空，或者轨迹值和excel值不一致，都标红
                            if (not val and excel_val) or (val and excel_val and excel_val.lower() != val.lower()):
                                self.highlight_cells.add((idx, col))
        self.status_label['text'] = f"核对完成，共{(self.match_result['__核对结果__']=='已核对').sum()}条已核对。"
        self.export_btn['state'] = 'normal'

    def export_result(self):
        # 导出带有标记和轨迹内容的Excel，带颜色
        file_path = filedialog.asksaveasfilename(defaultextension='.xlsx', filetypes=[('Excel Files', '*.xlsx')])
        if not file_path:
            return
        try:
            # 先用pandas导出
            self.match_result.to_excel(file_path, index=False)
            # 用openpyxl加颜色
            from openpyxl import load_workbook
            from openpyxl.styles import PatternFill
            wb = load_workbook(file_path)
            ws = wb.active
            # 列名到列号
            col_map = {cell.value: i+1 for i, cell in enumerate(ws[1])}
            cyan_fill = PatternFill(fill_type='solid', fgColor='CCFFFF')
            red_fill = PatternFill(fill_type='solid', fgColor='FF6666')
            for idx, row in self.match_result.iterrows():
                excel_row = idx + 2  # openpyxl从1开始，且有表头
                if row['__核对结果__'] == '已核对':
                    for col in ws[excel_row]:
                        col.fill = cyan_fill
                # 高亮不一致单元格
                for col_name in ['收件人城市', '收件人州', '收件人邮编']:
                    if (idx, col_name) in self.highlight_cells and col_name in col_map:
                        ws.cell(row=excel_row, column=col_map[col_name]).fill = red_fill
            wb.save(file_path)
            # 弹窗带"打开文件"按钮
            def open_file():
                import platform, subprocess
                if platform.system() == "Windows":
                    os.startfile(file_path)
                elif platform.system() == "Darwin":
                    subprocess.run(["open", file_path])
                else:
                    subprocess.run(["xdg-open", file_path])

            top = tk.Toplevel(self)
            top.title('导出成功')
            tk.Label(top, text=f'已导出到：{file_path}').pack(padx=20, pady=10)
            tk.Button(top, text='打开文件', command=open_file).pack(pady=(0,10))
            tk.Button(top, text='关闭', command=top.destroy).pack(pady=(0,10))
        except Exception as e:
            messagebox.showerror('错误', f'导出失败：{e}') 